﻿Imports System.IO
Imports System.Data.Odbc

Public Class import2export
    Inherits dooll.Web.UI.MasterPage

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click
        If Not FileUpload1 Is Nothing AndAlso Not FileUpload1.FileContent Is Nothing Then

            Try
                Dim errMessage As String
                Dim dt As DataTable = GetData(FileUpload1.FileContent, errMessage)
                If dt Is Nothing Then
                    Response.Write(errMessage)
                Else
                    Dim dgData As New DataGrid
                    dgData.DataSource = dt
                    dgData.DataBind()
                    Controls.Add(dgData)
                End If 
                
            Catch ex As Exception
                Response.Write(ex.Message)
            End Try

        End If
    End Sub


    Private Function GetData(inputStream As Stream, ByRef errMessage As String) As DataTable

        Dim dtFile As DataTable

        Dim sr As New StreamReader(inputStream, Text.Encoding.GetEncoding("gb2312"))

        Dim strTitle As String = sr.ReadLine()

        If String.IsNullOrEmpty(strTitle) Then
            errMessage = "没有找到任何数据行"
            Return Nothing
        Else
            dtFile = New DataTable()
        End If

        Dim strRowTitle As String() = strTitle.Split(CChar(","))

        For iColumn As Integer = 0 To strRowTitle.Length - 1
            If Not String.IsNullOrEmpty(strRowTitle(iColumn)) Then
                'add new column with name to the data table
                dtFile.Columns.Add(strRowTitle(iColumn).Trim())
            Else
                'file error:blank title
                dtFile.Columns.Add("col" + iColumn.ToString())
            End If
        Next

        Dim strRow As String
        While Not sr.EndOfStream
            Dim dr As DataRow = dtFile.NewRow()
            strRow = sr.ReadLine()
            Dim strRowContent As String() = strRow.Split(CChar(","))
            For iColumn As Integer = 0 To Math.Min(strRowContent.Length - 1, strRowTitle.Length - 1)
                dr.Item(iColumn) = strRowContent(iColumn).Trim()
            Next
            dtFile.Rows.Add(dr)
        End While
        Return dtFile
    End Function


    ''' <summary>
    ''' Get dataset from csv file.
    ''' </summary>
    ''' <param name="filepath"></param>
    ''' <param name="filename"></param>
    ''' <returns>Data Set</returns>
    Private Function GetDatasetFromCsv(filepath As String, filename As String) As DataSet
        Dim strconn As String = "driver={microsoft text driver (*.txt; *.csv)};dbq="
        strconn += filepath
        'filepath, for example: c:\
        strconn += ";extensions=asc,csv,tab,txt;"
        Dim objconn As New OdbcConnection(strconn)
        Dim dscsv As New DataSet()
        Try
            Dim strsql As String = "select * from " + filename
            'filename, for example: 1.csv
            Dim odbccsvdataadapter As New OdbcDataAdapter(strsql, objconn)

            odbccsvdataadapter.Fill(dscsv)
            Return dscsv
        Catch ex As Exception
            Throw ex
        End Try
    End Function





    ''' <summary>
    ''' Export to Csv File from dataset
    ''' </summary>
    ''' <param name="src"></param>
    ''' <param name="folderName">folderName</param>
    ''' <param name="strFileName">strFileName</param>
    ''' <returns></returns>
    Public Function ExportToCsv(src As DataSet, folderName As String, strFileName As String) As Boolean
        Dim csv As String = [String].Empty

        Dim writer As StreamWriter = Nothing

        Dim fileName As String = Server.MapPath("/") + folderName + "\" + strFileName

        Try
            If src Is Nothing OrElse src.Tables.Count = 0 Then
                Throw New Exception("dataset is null or has not table in dataset")
            End If


            Dim i As Integer = 0
            While i < src.Tables.Count

                If i > 0 Then

                    fileName = fileName.Substring(0, CInt(fileName.IndexOf("."))) + CStr(i) + fileName.Substring(fileName.IndexOf("."))
                End If



                writer = New StreamWriter(fileName)

                Dim dt As DataTable = src.Tables(i)

                Dim sb As New StringBuilder()

                Dim j As Integer = 0
                While j < dt.Columns.Count

                    Dim colName As String = dt.Columns(j).ColumnName

                    If colName.IndexOf(","c) > -1 Then

                        colName = colName.Insert(0, """").Insert(colName.Length + 1, """")
                    End If

                    sb.Append(colName)

                    If Not colName.Equals("") Then

                        If j <> dt.Columns.Count - 1 Then

                            sb.Append(",")
                        End If

                    End If
                    System.Math.Max(System.Threading.Interlocked.Increment(j), j - 1)
                End While

                writer.WriteLine(sb.ToString())

                sb = New StringBuilder()

                Dim temp As String = ""

                j = 0
                While j < dt.Rows.Count

                    Dim dr As DataRow = dt.Rows(j)

                    Dim k As Integer = 0
                    While k < dt.Columns.Count

                        Dim o As Object = dr(k)

                        If Not o Is Nothing Then

                            temp = o.ToString()
                        End If

                        If temp.IndexOf(","c) > -1 Then

                            temp = temp.Insert(0, """").Insert(temp.Length + 1, """")
                        End If

                        sb.Append(temp)

                        If k <> dt.Columns.Count - 1 Then

                            sb.Append(",")

                        End If
                        System.Math.Max(System.Threading.Interlocked.Increment(k), k - 1)
                    End While

                    writer.WriteLine(sb.ToString())

                    sb = New StringBuilder()
                    csv = sb.ToString()
                    System.Math.Max(System.Threading.Interlocked.Increment(j), j - 1)
                End While


                writer.Close()
                System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
            End While

            Dim strFilePath As String = Server.MapPath("/") + folderName
            If Not Directory.Exists(strFilePath) Then
                Directory.CreateDirectory(strFilePath)
            End If

            Dim strFullFileName As String = Server.MapPath("/") + folderName + "\" + fileName
            'FullFileName = Server.MapPath(FileName);
            'FileName
            Dim DownloadFile As New FileInfo(strFullFileName)
            If DownloadFile.Exists Then
                Response.Clear()
                Response.ClearHeaders()
                Response.Buffer = False
                Response.ContentType = "application/octet-stream"
                'Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DownloadFile.FullName, System.Text.Encoding.ASCII));
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.ASCII))
                Response.AppendHeader("Content-Length", DownloadFile.Length.ToString())
                Response.WriteFile(DownloadFile.FullName)
                'Response.End();
                Response.Flush()
            Else
                'not exist
                Throw New Exception("Export csv file does not exist!")


            End If

        Catch ex As Exception


            Throw New Exception("Save csv error", ex)
        Finally


            If Not writer Is Nothing Then
                writer.Close()

            End If
        End Try

        Return True

    End Function



End Class


